<?php
/**
 * Created by PhpStorm.
 * User: JSQ-SL
 * Date: 2018/11/19
 * Time: 15:37
 */

namespace app\index\table;

use app\index\controller\Login;
use think\Db;
use app\index\service\ExcelService;

class ConBuildingTable extends BaseTable
{
    protected static $view = 'V_CON_BUILDING';
    protected static $table = 'CON_BUILDING';
    protected static $pk = 'BUILDING_NO';
    protected static $configTable = 'CON_BUILDING_CONFIG';

    static public function table(){
        return Db::table(static::$view);
    }

    static public function add($data){
        $data[self::$pk] = static::getNextId();
        $data['IS_FIXED'] = 0;
        $data['INPUT_USER_ID'] = Login::getUserId();
        $data['INPUT_TIME'] = date('Y-m-d');

        self::toDate($data,'GET_DATE');
        self::toDate($data,'COMPLETION_DATE');
        self::toDate($data,'CERT_GET_DATE');
        self::toDate($data,'ENTER_DATE');
        self::toDate($data,'START_USE_DATE');
        self::toDate($data,'INPUT_TIME');
        Db::table(static::$table)->insert($data);
    }

    static public function addImport($data){
        $data['INPUT_USER_ID'] = Login::getUserId();
        $data['INPUT_TIME'] = date('Y-m-d');

        self::toDate($data,'GET_DATE');
        self::toDate($data,'COMPLETION_DATE');
        self::toDate($data,'CERT_GET_DATE');
        self::toDate($data,'ENTER_DATE');
        self::toDate($data,'START_USE_DATE');
        self::toDate($data,'INPUT_TIME');
        self::toDate($data,'FIXED_DATE');
        Db::table(static::$table)->insert($data);
    }

    static public function update($where,$data){
        self::toDate($data,'GET_DATE');
        self::toDate($data,'COMPLETION_DATE');
        self::toDate($data,'CERT_GET_DATE');
        self::toDate($data,'ENTER_DATE');
        self::toDate($data,'START_USE_DATE');
        self::toDate($data,'FIXED_DATE');
        Db::table(static::$table)->where($where)->update($data);
    }

    static public function delete($where){
        return Db::table(static::$table)->where($where)->delete();
    }

    public static function getConfigAdd(){
        return Db::table(static::$configTable)->where(['ADD_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getConfigEdit(){
        return Db::table(static::$configTable)->where(['EDIT_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getConfigPrint(){
        return Db::table(static::$configTable)->where(['PRINT_FLAG'=>'1'])->order('ID ASC')->select();
    }


    public static function getNextId(){
        $year = date('Y');
        $numMaxRecord = Db::table(static::$table)
            ->field('max(SUBSTR('.static::$pk.',10,6)) as NUM')
            ->where([['SUBSTR('.static::$pk.',6,4)','=',$year]])
            ->fetchSql(true)
            ->find();
        $numMaxRecord = Db::query($numMaxRecord);
        $num = $numMaxRecord[0]['NUM'];
        if ($num == NULL) {
            $num = 0;
        }
        $num++;
        return 'ZJ_FW'.$year.sprintf("%06d", $num);
    }

    public static function exportExcel($data,$fileName){

        $fieldList = [
            ['title'=>'资产编号','field'=>'BUILDING_NO'],
            ['title'=>'所属在建工程编号','field'=>'CONS_NO'],
            ['title'=>'所属在建工程名称','field'=>'CONS_NAME'],
            ['title'=>'资产分类代码','field'=>'ASSET_TYPE'],
            ['title'=>'资产名称','field'=>'BUILDING_NAME'],
            ['title'=>'原资产编号','field'=>'OLD_BUILDING_NO'],
            ['title'=>'坐落位置','field'=>'LOCATION'],
            ['title'=>'预算项目编号','field'=>'BUDGET_NO'],
            ['title'=>'产权形式','field'=>'PROPERTYRIGHT'],
            ['title'=>'取得方式','field'=>'GETTYPE'],
            ['title'=>'取得日期','field'=>'GET_DATE'],
            ['title'=>'预计使用年限（月）','field'=>'PRE_USE_LIFE'],
            ['title'=>'权属性质','field'=>'POWERTYPE'],
            ['title'=>'权属证明','field'=>'OWNER_CERT'],
            ['title'=>'权属证号','field'=>'OWNER_CERT_NO'],
            ['title'=>'权属年限','field'=>'OWNER_CERT_YEAR'],
            ['title'=>'房屋所有权人','field'=>'CERT_OWNER_NAME'],
            ['title'=>'设计用途','field'=>'USE_TYPE'],
            ['title'=>'建筑结构','field'=>'CONSTRU'],
            ['title'=>'竣工日期','field'=>'COMPLETION_DATE'],
            ['title'=>'发证时间','field'=>'CERT_GET_DATE'],
            ['title'=>'入账形式','field'=>'ENTER_TYPE'],
            ['title'=>'会计凭证号','field'=>'ACCOUNT_CERT_NO'],
            ['title'=>'财务入账日期','field'=>'ENTER_DATE'],
            ['title'=>'使用方向','field'=>'USEDIRECTION'],
            ['title'=>'使用状态','field'=>'STATUS'],
            ['title'=>'投入使用日期','field'=>'START_USE_DATE'],
            ['title'=>'管理部门','field'=>'COLLEGE'],
            ['title'=>'价值类型','field'=>'VALUETYPE'],
            ['title'=>'数量','field'=>'BUILDING_NUM'],
            ['title'=>'价值','field'=>'BUILDING_FEE'],
            ['title'=>'财政性资金','field'=>'FINANCE_FEE'],
            ['title'=>'非财政性资金','field'=>'NON_FINANCE_FEE'],
            ['title'=>'均价','field'=>'AVG_FEE'],
            ['title'=>'建筑面积','field'=>'BUILD_SIZE'],
            ['title'=>'其中：取暖面积','field'=>'HEAT_SIZE'],
            ['title'=>'自用面积','field'=>'SELF_SIZE'],
            ['title'=>'出借面积','field'=>'BORROW_SIZE'],
            ['title'=>'出租面积','field'=>'RENTAL_SIZE'],
            ['title'=>'对外投资面积','field'=>'INVESTMENT_SIZE'],
            ['title'=>'其他面积','field'=>'ELSE_SIZE'],
            ['title'=>'自用价值','field'=>'SELF_FEE'],
            ['title'=>'出借价值','field'=>'BORROW_FEE'],
            ['title'=>'出租价值','field'=>'RENTAL_FEE'],
            ['title'=>'对外投资价值','field'=>'INVESTMENT_FEE'],
            ['title'=>'其他价值','field'=>'ELSE_FEE'],
            ['title'=>'折旧状态','field'=>'DEPRECIATE'],
            ['title'=>'折旧方法','field'=>'DEPRECIATE_WAY'],
            ['title'=>'折旧年限（月）','field'=>'DEPRECIATE_LIFE'],
            ['title'=>'减值准备','field'=>'REDUCE_PRE'],
            ['title'=>'月折旧额','field'=>'DEPRECIATE_PER_MON'],
            ['title'=>'残值率','field'=>'DEPRECIATE_PERCENTAGE'],
            ['title'=>'已提折旧月数','field'=>'DEPRECIATED_MONTH'],
            ['title'=>'累计折旧','field'=>'ACC_DEPRECIATE'],
            ['title'=>'备注','field'=>'REMARK'],
            ['title'=>'是否转固','field'=>'IS_FIXED'],
            ['title'=>'转固资产编号','field'=>'FIXED_NO'],
            ['title'=>'转固日期','field'=>'FIXED_DATE'],
            ['title'=>'转固人','field'=>'FIXED_USER_NAME'],
            ['title'=>'制单人','field'=>'INPUT_NAME'],
            ['title'=>'制单时间','field'=>'INPUT_TIME'],
        ];

        $excel = new ExcelService();
        $excel->exportExcel($data,$fileName,$fieldList);
    }

    const BUILDING_FIELD_LIST = [
        "BUILDING_NO","CONS_NO","ASSET_TYPE_ID","BUILDING_NAME","OLD_BUILDING_NO","LOCATION","BUDGET_NO","PROPERTYRIGHT_ID","GETTYPE_ID","GET_DATE","PRE_USE_LIFE","POWERTYPE_ID","OWNER_CERT","OWNER_CERT_NO","OWNER_CERT_YEAR","CERT_OWNER_NAME","USE_TYPE","CONSTRU_ID","COMPLETION_DATE","CERT_GET_DATE","ENTER_TYPE_ID","ACCOUNT_CERT_NO","ENTER_DATE","USEDIRECTION_ID","STATUS_ID","START_USE_DATE","COLLEGE_ID","VALUETYPE_ID","BUILDING_NUM","BUILDING_FEE","FINANCE_FEE","NON_FINANCE_FEE","AVG_FEE","BUILD_SIZE","HEAT_SIZE","SELF_SIZE","BORROW_SIZE","RENTAL_SIZE","INVESTMENT_SIZE","ELSE_SIZE","SELF_FEE","BORROW_FEE","RENTAL_FEE","INVESTMENT_FEE","ELSE_FEE","DEPRECIATE_STATUS_ID","DEPRECIATE_WAY","DEPRECIATE_LIFE","REDUCE_PRE","DEPRECIATE_PER_MON","DEPRECIATE_PERCENTAGE","DEPRECIATED_MONTH","ACC_DEPRECIATE","REMARK","IS_FIXED","FIXED_NO","FIXED_DATE","FIXED_USER_ID"
    ];

    public static function getSessionConditions() {
        $condition = null;
        $listCondition = Login::getListConditionConBuilding();
        if (!empty($listCondition['BUILDING_NO'])) {
            $value = $listCondition['BUILDING_NO'];
            $condition[] = ['BUILDING_NO','like',"$value%"];
        }
        if (!empty($listCondition['BUILDING_NAME'])) {
            $value = $listCondition['BUILDING_NAME'];
            $condition[] = ['BUILDING_NAME','like',"%$value%"];
        }
        if (!empty($listCondition['CONS_ID'])) {
            $value = $listCondition['CONS_ID'];
            $condition[] = ['CONS_ID','=',$value];
        }
        if (!empty($listCondition['ASSET_TYPE_ID'])) {
            $value = $listCondition['ASSET_TYPE_ID'];
            $condition[] = ['ASSET_TYPE_ID','=',$value];
        }
        if (!empty($listCondition['PROPERTYRIGHT_ID'])) {
            $value = $listCondition['PROPERTYRIGHT_ID'];
            $condition[] = ['PROPERTYRIGHT_ID','=',$value];
        }
        if (!empty($listCondition['POWERTYPE_ID'])) {
            $value = $listCondition['POWERTYPE_ID'];
            $condition[] = ['POWERTYPE_ID','=',$value];
        }
        if (!empty($listCondition['GETTYPE_ID'])) {
            $value = $listCondition['GETTYPE_ID'];
            $condition[] = ['GETTYPE_ID','=',$value];
        }
        if (!empty($listCondition['CONSTRU_ID'])) {
            $value = $listCondition['CONSTRU_ID'];
            $condition[] = ['CONSTRU_ID','=',$value];
        }
        if (!empty($listCondition['ENTER_TYPE_ID'])) {
            $value = $listCondition['ENTER_TYPE_ID'];
            $condition[] = ['ENTER_TYPE_ID','=',$value];
        }
        if (!empty($listCondition['USEDIRECTION_ID'])) {
            $value = $listCondition['USEDIRECTION_ID'];
            $condition[] = ['USEDIRECTION_ID','=',$value];
        }
        if (!empty($listCondition['STATUS_ID'])) {
            $value = $listCondition['STATUS_ID'];
            $condition[] = ['STATUS_ID','=',$value];
        }
        if (!empty($listCondition['COLLEGE_ID'])) {
            $value = $listCondition['COLLEGE_ID'];
            $condition[] = ['COLLEGE_ID','=',$value];
        }
        if (!empty($listCondition['VALUETYPE_ID'])) {
            $value = $listCondition['VALUETYPE_ID'];
            $condition[] = ['VALUETYPE_ID','=',$value];
        }
        if (!empty($listCondition['DEPRECIATE_STATUS_ID'])) {
            $value = $listCondition['DEPRECIATE_STATUS_ID'];
            $condition[] = ['DEPRECIATE_STATUS_ID','=',$value];
        }

        if (!empty($listCondition['GET_DATE_START'])) {
            $value = Db::raw("to_date('{$listCondition['GET_DATE_START']}','yyyy-mm-dd')");
            $condition[] = ['GET_DATE','>=',$value];
        }
        if (!empty($listCondition['GET_DATE_END'])) {
            $value = Db::raw("to_date('{$listCondition['GET_DATE_END']}','yyyy-mm-dd')");
            $condition[] = ['GET_DATE','<=',$value];
        }

        if (!empty($listCondition['BUILDING_FEE_START'])) {
            $value = $listCondition['BUILDING_FEE_START'];
            $condition[] = ['BUILDING_FEE','>=',$value];
        }
        if (!empty($listCondition['BUILDING_FEE_END'])) {
            $value = $listCondition['BUILDING_FEE_END'];
            $condition[] = ['BUILDING_FEE','<=',$value];
        }
        if (!empty($listCondition['BUILD_SIZE_START'])) {
            $value = $listCondition['BUILD_SIZE_START'];
            $condition[] = ['BUILD_SIZE','>=',$value];
        }
        if (!empty($listCondition['BUILD_SIZE_END'])) {
            $value = $listCondition['BUILD_SIZE_END'];
            $condition[] = ['BUILD_SIZE','<=',$value];
        }

        return $condition;
    }
}